package com.fpt.group3.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.fpt.group3.form.Country;
import com.fpt.group3.service.exception.SystemException;

/**
 * country DAO access to data.
 * @author John Tran
 */
public class CountryDAOImpl implements CountryDAO {

    @Override
    public final List<Country> listCountry() throws SystemException {
        LinkedList<Country> countryList = new LinkedList<Country>();
        Country co = null;
        String sql = "SELECT COUNTRYID, COUNTRYNAME FROM country";
        Statement stmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            Connection con = conn.open();
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                co = new Country();
                co.setCountryID(rs.getInt("COUNTRYID"));
                co.setCountryName(rs.getString("COUNTRYNAME"));
                countryList.add(co);
            }
        } catch (SystemException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return countryList;
    }

    /**
     * get country name.
     * @return integer, countryID
     * @param reName name of country
     * @exception SystemException throws system exception
     */
    public final int getCountryIdByName(final String reName)
                     throws SystemException {
        String sql = "SELECT COUNTRYID FROM country WHERE COUNTRYNAME = ?";
        PreparedStatement pstmt = null;
        ConnectDB conn = new ConnectDB();
        try {
            pstmt = conn.open().prepareStatement(sql);
            System.out.println("Country Name = " + reName);
            pstmt.setString(1, reName);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } catch (SystemException e) {
            e.printStackTrace();
            return -1;
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }
}
